{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "a68d0f75-15ea-4e43-8c6c-7ee3c2ff57a6",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pyspark.sql.dataframe.DataFrame'>\nread ok!\n20130701,32488348,5525022\n20130702,29037390,2554548\n20130703,27270770,5953867\n20130704,18321185,6410729\n20130705,11648749,2763587\n20130706,36751272,1616635\n20130707,8962232,3982735\n20130708,57258266,8347729\n20130709,26798941,3473059\n20130710,30696506,2597169\n20130711,44075197,3508800\n20130712,34183904,8492573\n20130713,15164717,3482829\n20130714,22615303,2784107\n20130715,48128555,13107943\n20130716,50622847,11864981\n20130717,29015682,10911513\n20130718,24234505,11765356\n20130719,33680124,9244769\n20130720,20439079,4601143\n20130721,21142394,2681331\n20130722,40448896,19144267\n20130723,58136147,24404051\n20130724,48422518,36258592\n20130725,57433418,38212836\n20130726,44721817,39192369\n20130727,17194451,15058893\n20130728,36255382,7683211\n20130729,53512076,18599364\n20130730,47481243,13048582\n20130731,54569637,9534040\n20130801,53369962,18864468\n20130802,38064536,40150769\n20130803,21595789,20701797\n20130804,45745254,8263965\n20130805,43632203,15797507\n20130806,50866184,16401387\n20130807,43908081,29708706\n20130808,44493490,29551691\n20130809,33425186,30131015\n20130810,20615307,26614408\n20130811,55519543,15372680\n20130812,94520502,28586669\n20130813,48720919,28790328\n20130814,30541167,15031683\n20130815,42421222,52659327\n20130816,41683536,33739844\n20130817,17670519,4674983\n20130818,59703092,15218300\n20130819,109184209,28339260\n20130820,53675509,30131225\n20130821,38184446,29983342\n20130822,89130737,25000672\n20130823,37878575,25680323\n20130824,44367268,14316598\n20130825,43306288,18117808\n20130826,74496541,20637986\n20130827,45268028,60137633\n20130828,46602319,33696861\n20130829,47518666,35944968\n20130830,56488844,20630752\n20130831,47655303,22012016\n20130901,56239802,59339949\n20130902,140844739,17785524\n20130903,80507880,39328144\n20130904,33366708,27452600\n20130905,38716505,21800904\n20130906,38923186,33417903\n20130907,34076183,20344424\n20130908,38796909,14919122\n20130909,49312473,45621186\n20130910,94684143,37128363\n20130911,98944459,71674082\n20130912,68573684,45147220\n20130913,71655946,60512675\n20130914,30975443,36312660\n20130915,37059683,25020715\n20130916,161656210,45184589\n20130917,76204815,58260798\n20130918,42926608,47203221\n20130919,24778048,11418512\n20130920,28365762,15188254\n20130921,32406340,26596179\n20130922,67514169,47962055\n20130923,54658160,58285879\n20130924,74250859,56680792\n20130925,47274168,74838162\n20130926,68718693,48467529\n20130927,42166121,27639603\n20130928,39995798,58105720\n20130929,34433848,40295202\n20130930,28398050,30181353\n20131001,19137499,12813267\n20131002,11562708,7233874\n20131003,10101194,6223263\n20131004,19907689,23412350\n20131005,26364686,13966453\n20131006,38730653,13464528\n20131007,42797733,28894400\n20131008,109806912,77856096\n20131009,72422299,33999376\n20131010,72094613,52244531\n20131011,82674757,27666473\n20131012,81901136,31322983\n20131013,71149981,31850475\n20131014,130315300,40652625\n20131015,85704304,35099198\n20131016,100948091,51261982\n20131017,87203217,23274764\n20131018,98151450,15938355\n20131019,53148443,26684697\n20131020,47766681,50884342\n20131021,106030290,45781403\n20131022,145611496,96172537\n20131023,106093112,73788462\n20131024,69530830,58183921\n20131025,69936453,52225802\n20131026,55752506,49357211\n20131027,68007543,46520570\n20131028,129993375,55089464\n20131029,102050144,43052468\n20131030,109663260,63908701\n20131031,90926701,55607833\n20131101,150904945,66933119\n20131102,62132300,26705506\n20131103,68707870,30895754\n20131104,300027403,130970051\n20131105,180904814,82689864\n20131106,181474910,131707820\n20131107,167963464,83306452\n20131108,231749471,92856307\n20131109,126467028,45885771\n20131110,164813471,85293644\n20131111,147986439,227124951\n20131112,148469647,131263524\n20131113,174554976,79121440\n20131114,167996058,52554150\n20131115,124270704,58210254\n20131116,118085705,28996272\n20131117,111786622,42358155\n20131118,195766477,136916782\n20131119,131365419,70308235\n20131120,166163002,86951218\n20131121,221166938,74733962\n20131122,128528687,82717921\n20131123,89109018,48882131\n20131124,102394991,85555947\n20131125,147143602,70113127\n20131126,258037702,65802983\n20131127,134774128,86129498\n20131128,139760425,61453591\n20131129,122536344,123982166\n20131130,116865492,119660311\n20131201,137889992,94632233\n20131202,345313258,128465086\n20131203,268666856,101662045\n20131204,219456372,69981329\n20131205,190825287,118256819\n20131206,152197159,82625571\n20131207,138882605,71916047\n20131208,124886903,76314292\n20131209,174652812,133355299\n20131210,179471612,147147341\n20131211,239916977,110314592\n20131212,140360007,92090275\n20131213,164672690,90778214\n20131214,129045596,52234651\n20131215,205882387,54757146\n20131216,196489931,156157326\n20131217,193980797,116537843\n20131218,180215804,202220872\n20131219,233631357,164023344\n20131220,133867400,153678963\n20131221,112000779,83151594\n20131222,154543217,83998249\n20131223,198747367,138478245\n20131224,305406334,118607911\n20131225,380076148,172755480\n20131226,220287409,241852564\n20131227,211118967,163522548\n20131228,192808006,61383220\n20131229,161473347,55129989\n20131230,286358778,179826624\n20131231,265851934,134232530\n20140101,330926565,77367755\n20140102,434956739,190155450\n20140103,342074805,127714255\n20140104,186085910,99869074\n20140105,206030707,156781996\n20140106,442494042,190917629\n20140107,589726496,137972793\n20140108,264025160,213880074\n20140109,280698487,140391237\n20140110,237797636,117259153\n20140111,243403438,71530182\n20140112,228255344,141505812\n20140113,447207050,178923772\n20140114,356907128,159778389\n20140115,391408718,177618247\n20140116,374947083,153167426\n20140117,345426853,127385210\n20140118,233200688,142869842\n20140119,185082022,117629351\n20140120,335761027,442996084\n20140121,412854611,302825136\n20140122,498730606,292856188\n20140123,481450097,209024328\n20140124,614103894,345829001\n20140125,440854623,185524856\n20140126,528402520,331752519\n20140127,657199484,280645861\n20140128,843424742,248334316\n20140129,952479658,215164666\n20140130,227916211,105288105\n20140131,87324175,48132389\n20140201,64287172,61527060\n20140202,57912761,24395720\n20140203,84329848,46109194\n20140204,83653646,44927333\n20140205,209043990,95407704\n20140206,227860276,111635336\n20140207,578818221,273482213\n20140208,358255468,183432237\n20140209,341911234,262954049\n20140210,647465140,431753411\n20140211,818803205,243058162\n20140212,763009770,354585919\n20140213,626698794,362757986\n20140214,490710434,263261899\n20140215,255393179,121463693\n20140216,331324628,183057224\n20140217,706118060,405809267\n20140218,593563145,271125324\n20140219,408367966,323990451\n20140220,478925191,230855410\n20140221,353981687,178914448\n20140222,282022706,134735118\n20140223,337053711,195214052\n20140224,656317045,473470156\n20140225,563505889,299155352\n20140226,680801145,284819682\n20140227,672909288,492786036\n20140228,428721754,322030204\n20140301,362865580,211279011\n20140302,276202230,246199417\n20140303,505305862,513017360\n20140304,524146340,250562978\n20140305,454295491,209072753\n20140306,561787770,243149884\n20140307,380139779,291087220\n20140308,243274169,140323202\n20140309,244752519,206312503\n20140310,497338076,308040624\n20140311,430500816,496039886\n20140312,377007897,416491268\n20140313,346286910,316973542\n20140314,315897431,311575572\n20140315,287407002,242799048\n20140316,269387391,390584547\n20140317,339008082,440624592\n20140318,435479117,410240726\n20140319,359014064,429298917\n20140320,365011495,336076380\n20140321,282351818,259655286\n20140322,191700135,138039412\n20140323,167456369,186443311\n20140324,313180334,437825259\n20140325,314345006,312710515\n20140326,272935544,450254233\n20140327,266231082,359071642\n20140328,225966355,405443946\n20140329,160250985,155006056\n20140330,205533934,264714811\n20140331,398884905,423852634\n20140401,453320585,277429358\n20140402,355347118,272612066\n20140403,363877120,266605457\n20140404,251895894,200192637\n20140405,202336542,163199682\n20140406,129477254,139576683\n20140407,196936223,176966561\n20140408,354770149,250015131\n20140409,383347565,289330278\n20140410,386567460,286914864\n20140411,237829882,277077434\n20140412,177642053,123295320\n20140413,208172985,178934722\n20140414,309853269,415986984\n20140415,428681231,285293076\n20140416,387847838,255914640\n20140417,355792647,265341592\n20140418,239300383,225952909\n20140419,268729366,146374940\n20140420,191259529,161057781\n20140421,301134667,295635256\n20140422,285248757,268810141\n20140423,313677307,278470936\n20140424,318358891,224536754\n20140425,220927432,227764292\n20140426,151625415,158122962\n20140427,146837951,191915377\n20140428,324937272,327724735\n20140429,330607104,307578349\n20140430,260091330,281835975\n20140501,193045106,143362755\n20140502,125336258,121222064\n20140503,185094488,199568043\n20140504,303087562,413222034\n20140505,370924149,309330781\n20140506,318002728,341108696\n20140507,417327518,239372999\n20140508,392838756,273187499\n20140509,281479009,247743971\n20140510,287240171,147248328\n20140511,182424063,152945581\n20140512,325108597,293952908\n20140513,275241493,257918375\n20140514,305474522,316225442\n20140515,313367089,238307643\n20140516,231967423,282094916\n20140517,170983868,145854372\n20140518,164419642,153440019\n20140519,259077930,293791406\n20140520,453955303,260040720\n20140521,297799722,250223726\n20140522,344636549,251108485\n20140523,249546195,229000787\n20140524,160073254,154409868\n20140525,166943526,231004758\n20140526,344890868,274707572\n20140527,299049555,321965845\n20140528,276134813,415891684\n20140529,320549863,313827800\n20140530,226547701,312802179\n20140531,146823669,142862063\n20140601,183489775,149829253\n20140602,158219402,170409506\n20140603,270887462,385622582\n20140604,274460744,303978838\n20140605,380042567,355645445\n20140606,301413900,274862067\n20140607,187801995,146203577\n20140608,302171269,169525332\n20140609,366114374,287520152\n20140610,354031597,298190025\n20140611,327661453,246127540\n20140612,332365185,236467885\n20140613,216923770,386799040\n20140614,181574530,229916191\n20140615,166080126,116623756\n20140616,387308484,492349489\n20140617,270350693,502560223\n20140618,335262709,421920230\n20140619,338609087,284956260\n20140620,251582530,286583065\n20140621,177999186,231003775\n20140622,147180819,361285652\n20140623,232227670,373779624\n20140624,245450766,428471509\n20140625,264663201,547295931\n20140626,297628039,418742109\n20140627,264282703,399444352\n20140628,153826161,283400236\n20140629,158801540,261170799\n20140630,334054112,456547794\n20140701,384428753,374164541\n20140702,384555819,328950951\n20140703,297894643,289009780\n20140704,211649838,264494550\n20140705,169383796,272535138\n20140706,199569025,195530758\n20140707,272182847,317612569\n20140708,224240103,340453063\n20140709,278005555,269642881\n20140710,283095921,326009240\n20140711,208671021,240050748\n20140712,177644343,149081488\n20140713,179759885,199459990\n20140714,254797524,284753279\n20140715,334810012,261722182\n20140716,394890140,234775948\n20140717,253011280,298279385\n20140718,208959595,208671287\n20140719,210318023,155464283\n20140720,176449304,174462836\n20140721,378088594,434191479\n20140722,243084133,369043423\n20140723,265461894,308353077\n20140724,277044480,347622431\n20140725,181641088,262874791\n20140726,128268053,282653341\n20140727,151406251,166610652\n20140728,371762756,345986909\n20140729,228093046,303480103\n20140730,209917272,250117716\n20140731,191728916,277194379\n20140801,374884735,252540858\n20140802,189092130,172250225\n20140803,173825397,127125217\n20140804,330640884,322907524\n20140805,394780870,221706539\n20140806,288821016,282346594\n20140807,247646474,253659514\n20140808,233903717,311648757\n20140809,160262764,163611708\n20140810,259534870,189909225\n20140811,331550471,418603336\n20140812,258493673,309754858\n20140813,261506619,303975517\n20140814,257702660,211939431\n20140815,244551620,236516007\n20140816,215059736,219214339\n20140817,149978271,139564084\n20140818,298499146,259169016\n20140819,266401973,254929877\n20140820,308378692,202452782\n20140821,251763517,219963356\n20140822,246316056,179349206\n20140823,141412027,199377531\n20140824,130195484,191080151\n20140825,309574223,312413411\n20140826,306945089,285478563\n20140827,302194801,468164147\n20140828,245082751,297893861\n20140829,267554713,273756380\n20140830,199708772,196374134\n20140831,275090213,292943033\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "#导入数据表\n",
    "#以dataframe读取：\n",
    "user_profile = spark.read.format(\"csv\").option(\"header\", \"true\").load(\"dbfs:/FileStore/tables/user_profile_table.csv\")\n",
    "user_balance=spark.read.format(\"csv\").option(\"header\",\"true\").load(\"dbfs:/FileStore/tables/user_balance_table.csv\")\n",
    "#以RDD读取：\n",
    "user_profile_rdd=spark.sparkContext.textFile(\"dbfs:/FileStore/tables/user_profile_table.csv\")\n",
    "user_balance_rdd=spark.sparkContext.textFile(\"dbfs:/FileStore/tables/user_balance_table.csv\")\n",
    "\n",
    "print(type(user_profile))\n",
    "\n",
    "print('read ok!')\n",
    "\n",
    "#之前使用dataframe进行处理的\n",
    "# temp_table_name = \"user_balance_table_csv\"\n",
    "# user_balance.createOrReplaceTempView(temp_table_name)\n",
    "# temp_table_name2 = \"user_profile_table_csv\"\n",
    "# user_profile.createOrReplaceTempView(temp_profile_name2)\n",
    "# print('table ok!')\n",
    "\n",
    "# df_balance=user_balance.toPandas()\n",
    "# df_balance.head(10)\n",
    "# type(user_profile)\n",
    "\n",
    "#任务一：统计每天流量\n",
    "#使用RDD进行数据处理\n",
    "header = user_balance_rdd.first()  # 去除表头\n",
    "user_balance_rdd_no_header = user_balance_rdd.filter(lambda x: x != header)\n",
    "\n",
    "user_balance_rdd_processed = user_balance_rdd_no_header.map(lambda x: x.split(',')) \\\n",
    "    .map(lambda x: (x[1], (float(x[4]), float(x[8]))))\n",
    "\n",
    "daily_funds_flow = user_balance_rdd_processed.reduceByKey(lambda x, y: (x[0] + y[0], x[1] + y[1]))\n",
    "daily_funds_flow_sorted = daily_funds_flow.sortByKey(ascending=True)\n",
    "\n",
    "for date, (total_purchase, total_redeem) in daily_funds_flow_sorted.collect():\n",
    "    print(f\"{date},{int(total_purchase)},{int(total_redeem)}\")\n",
    "\n",
    "# type(daily_funds_flow_sorted)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "da7854e0-439c-4e57-89ad-b4224e3b0f8d",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# from pyspark.sql import SparkSession\n",
    "# from pyspark.sql.functions import sum, col, format_number\n",
    "\n",
    "#任务一（用dataframe实现，因此注释掉）\n",
    "\n",
    "# grouped_rdd = user_balance.groupBy(lambda x: x.report_date)\n",
    "\n",
    "# def aggregate_values(values):\n",
    "#     total_purchase_amt = sum(value.total_purchase_amt for value in values)\n",
    "#     total_redeem_amt = sum(value.total_redeem_amt for value in values)\n",
    "#     return (total_purchase_amt, total_redeem_amt)\n",
    "\n",
    "# # 使用mapValues和aggregateByKey来聚合每个组的值\n",
    "# aggregated_rdd = grouped_rdd.mapValues(aggregate_values).map(lambda x: (x[0], x[1][0], x[1][1]))\n",
    "\n",
    "# # 3. 格式化数字\n",
    "# formatted_rdd = aggregated_rdd.map(lambda x: (x[0], round(x[1], 2), round(x[2], 2)))\n",
    "\n",
    "# type(formatted_rdd)\n",
    "\n",
    "# spark = SparkSession.builder.getOrCreate()\n",
    "\n",
    "# # 将RDD转换回DataFrame\n",
    "# daily_total_df = formatted_rdd.map(lambda x: Row(report_date=x[0], total_purchase_amt=x[1], total_redeem_amt=x[2])).toDF()\n",
    "\n",
    "# # 显示DataFrame\n",
    "# daily_total_df.show()\n",
    "\n",
    "\n",
    "# daily_total_amt = user_balance.groupBy(\"report_date\") \\\n",
    "#     .agg(\n",
    "#         sum(\"total_purchase_amt\").alias(\"total_purchase_amt\"),\n",
    "#         sum(\"total_redeem_amt\").alias(\"total_redeem_amt\")\n",
    "#     ) \\\n",
    "#     .withColumn(\"total_purchase_amt\", format_number(col(\"total_purchase_amt\"), 2)) \\\n",
    "#     .withColumn(\"total_redeem_amt\", format_number(col(\"total_redeem_amt\"), 2))\n",
    "\n",
    "# print(\"ok!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "ad9d83fe-8d6c-4d0d-a951-b231c51866e5",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+------------------+----------------+\n|report_date|total_purchase_amt|total_redeem_amt|\n+-----------+------------------+----------------+\n|   20130701|          32488348|         5525022|\n|   20130702|          29037390|         2554548|\n|   20130703|          27270770|         5953867|\n|   20130704|          18321185|         6410729|\n|   20130705|          11648749|         2763587|\n|   20130706|          36751272|         1616635|\n|   20130707|           8962232|         3982735|\n|   20130708|          57258266|         8347729|\n|   20130709|          26798941|         3473059|\n|   20130710|          30696506|         2597169|\n|   20130711|          44075197|         3508800|\n|   20130712|          34183904|         8492573|\n|   20130713|          15164717|         3482829|\n|   20130714|          22615303|         2784107|\n|   20130715|          48128555|        13107943|\n|   20130716|          50622847|        11864981|\n|   20130717|          29015682|        10911513|\n|   20130718|          24234505|        11765356|\n|   20130719|          33680124|         9244769|\n|   20130720|          20439079|         4601143|\n+-----------+------------------+----------------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "# 任务1-1规范格式，将科学计数法转化为普通数值串（用dataframe实现，因此注释掉）\n",
    "\n",
    "# from pyspark.sql import functions as F\n",
    "\n",
    "\n",
    "# # 首先将数值转换为整数类型，然后格式化为没有小数点的字符串\n",
    "# daily_total_amt = user_balance.groupBy(\"report_date\") \\\n",
    "#     .agg(\n",
    "#         F.sum(\"total_purchase_amt\").alias(\"total_purchase_amt\"),\n",
    "#         F.sum(\"total_redeem_amt\").alias(\"total_redeem_amt\")\n",
    "#     ) \\\n",
    "#     .withColumn(\"total_purchase_amt\", F.col(\"total_purchase_amt\").cast('integer')) \\\n",
    "#     .withColumn(\"total_redeem_amt\", F.col(\"total_redeem_amt\").cast('integer'))\n",
    "\n",
    "# #排序\n",
    "# sorted_daily_total_amt = daily_total_amt.orderBy(\"report_date\")\n",
    "# sorted_daily_total_amt.show()\n",
    "# sorted_daily_total_amt.write.csv('task1_1_new', mode='overwrite')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "b0fe4d41-a702-45c5-98d8-96f77598e9c4",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>_c0</th><th>_c1</th><th>_c2</th></tr></thead><tbody><tr><td>20130701</td><td>32488348</td><td>5525022</td></tr><tr><td>20130702</td><td>29037390</td><td>2554548</td></tr><tr><td>20130703</td><td>27270770</td><td>5953867</td></tr><tr><td>20130704</td><td>18321185</td><td>6410729</td></tr><tr><td>20130705</td><td>11648749</td><td>2763587</td></tr><tr><td>20130706</td><td>36751272</td><td>1616635</td></tr><tr><td>20130707</td><td>8962232</td><td>3982735</td></tr><tr><td>20130708</td><td>57258266</td><td>8347729</td></tr><tr><td>20130709</td><td>26798941</td><td>3473059</td></tr><tr><td>20130710</td><td>30696506</td><td>2597169</td></tr><tr><td>20130711</td><td>44075197</td><td>3508800</td></tr><tr><td>20130712</td><td>34183904</td><td>8492573</td></tr><tr><td>20130713</td><td>15164717</td><td>3482829</td></tr><tr><td>20130714</td><td>22615303</td><td>2784107</td></tr><tr><td>20130715</td><td>48128555</td><td>13107943</td></tr><tr><td>20130716</td><td>50622847</td><td>11864981</td></tr><tr><td>20130717</td><td>29015682</td><td>10911513</td></tr><tr><td>20130718</td><td>24234505</td><td>11765356</td></tr><tr><td>20130719</td><td>33680124</td><td>9244769</td></tr><tr><td>20130720</td><td>20439079</td><td>4601143</td></tr><tr><td>20130721</td><td>21142394</td><td>2681331</td></tr><tr><td>20130722</td><td>40448896</td><td>19144267</td></tr><tr><td>20130723</td><td>58136147</td><td>24404051</td></tr><tr><td>20130724</td><td>48422518</td><td>36258592</td></tr><tr><td>20130725</td><td>57433418</td><td>38212836</td></tr><tr><td>20130726</td><td>44721817</td><td>39192369</td></tr><tr><td>20130727</td><td>17194451</td><td>15058893</td></tr><tr><td>20130728</td><td>36255382</td><td>7683211</td></tr><tr><td>20130729</td><td>53512076</td><td>18599364</td></tr><tr><td>20130730</td><td>47481243</td><td>13048582</td></tr><tr><td>20130731</td><td>54569637</td><td>9534040</td></tr><tr><td>20130801</td><td>53369962</td><td>18864468</td></tr><tr><td>20130802</td><td>38064536</td><td>40150769</td></tr><tr><td>20130803</td><td>21595789</td><td>20701797</td></tr><tr><td>20130804</td><td>45745254</td><td>8263965</td></tr><tr><td>20130805</td><td>43632203</td><td>15797507</td></tr><tr><td>20130806</td><td>50866184</td><td>16401387</td></tr><tr><td>20130807</td><td>43908081</td><td>29708706</td></tr><tr><td>20130808</td><td>44493490</td><td>29551691</td></tr><tr><td>20130809</td><td>33425186</td><td>30131015</td></tr><tr><td>20130810</td><td>20615307</td><td>26614408</td></tr><tr><td>20130811</td><td>55519543</td><td>15372680</td></tr><tr><td>20130812</td><td>94520502</td><td>28586669</td></tr><tr><td>20130813</td><td>48720919</td><td>28790328</td></tr><tr><td>20130814</td><td>30541167</td><td>15031683</td></tr><tr><td>20130815</td><td>42421222</td><td>52659327</td></tr><tr><td>20130816</td><td>41683536</td><td>33739844</td></tr><tr><td>20130817</td><td>17670519</td><td>4674983</td></tr><tr><td>20130818</td><td>59703092</td><td>15218300</td></tr><tr><td>20130819</td><td>109184209</td><td>28339260</td></tr><tr><td>20130820</td><td>53675509</td><td>30131225</td></tr><tr><td>20130821</td><td>38184446</td><td>29983342</td></tr><tr><td>20130822</td><td>89130737</td><td>25000672</td></tr><tr><td>20130823</td><td>37878575</td><td>25680323</td></tr><tr><td>20130824</td><td>44367268</td><td>14316598</td></tr><tr><td>20130825</td><td>43306288</td><td>18117808</td></tr><tr><td>20130826</td><td>74496541</td><td>20637986</td></tr><tr><td>20130827</td><td>45268028</td><td>60137633</td></tr><tr><td>20130828</td><td>46602319</td><td>33696861</td></tr><tr><td>20130829</td><td>47518666</td><td>35944968</td></tr><tr><td>20130830</td><td>56488844</td><td>20630752</td></tr><tr><td>20130831</td><td>47655303</td><td>22012016</td></tr><tr><td>20130901</td><td>56239802</td><td>59339949</td></tr><tr><td>20130902</td><td>140844739</td><td>17785524</td></tr><tr><td>20130903</td><td>80507880</td><td>39328144</td></tr><tr><td>20130904</td><td>33366708</td><td>27452600</td></tr><tr><td>20130905</td><td>38716505</td><td>21800904</td></tr><tr><td>20130906</td><td>38923186</td><td>33417903</td></tr><tr><td>20130907</td><td>34076183</td><td>20344424</td></tr><tr><td>20130908</td><td>38796909</td><td>14919122</td></tr><tr><td>20130909</td><td>49312473</td><td>45621186</td></tr><tr><td>20130910</td><td>94684143</td><td>37128363</td></tr><tr><td>20130911</td><td>98944459</td><td>71674082</td></tr><tr><td>20130912</td><td>68573684</td><td>45147220</td></tr><tr><td>20130913</td><td>71655946</td><td>60512675</td></tr><tr><td>20130914</td><td>30975443</td><td>36312660</td></tr><tr><td>20130915</td><td>37059683</td><td>25020715</td></tr><tr><td>20130916</td><td>161656210</td><td>45184589</td></tr><tr><td>20130917</td><td>76204815</td><td>58260798</td></tr><tr><td>20130918</td><td>42926608</td><td>47203221</td></tr><tr><td>20130919</td><td>24778048</td><td>11418512</td></tr><tr><td>20130920</td><td>28365762</td><td>15188254</td></tr><tr><td>20130921</td><td>32406340</td><td>26596179</td></tr><tr><td>20130922</td><td>67514169</td><td>47962055</td></tr><tr><td>20130923</td><td>54658160</td><td>58285879</td></tr><tr><td>20130924</td><td>74250859</td><td>56680792</td></tr><tr><td>20130925</td><td>47274168</td><td>74838162</td></tr><tr><td>20130926</td><td>68718693</td><td>48467529</td></tr><tr><td>20130927</td><td>42166121</td><td>27639603</td></tr><tr><td>20130928</td><td>39995798</td><td>58105720</td></tr><tr><td>20130929</td><td>34433848</td><td>40295202</td></tr><tr><td>20130930</td><td>28398050</td><td>30181353</td></tr><tr><td>20131001</td><td>19137499</td><td>12813267</td></tr><tr><td>20131002</td><td>11562708</td><td>7233874</td></tr><tr><td>20131003</td><td>10101194</td><td>6223263</td></tr><tr><td>20131004</td><td>19907689</td><td>23412350</td></tr><tr><td>20131005</td><td>26364686</td><td>13966453</td></tr><tr><td>20131006</td><td>38730653</td><td>13464528</td></tr><tr><td>20131007</td><td>42797733</td><td>28894400</td></tr><tr><td>20131008</td><td>109806912</td><td>77856096</td></tr><tr><td>20131009</td><td>72422299</td><td>33999376</td></tr><tr><td>20131010</td><td>72094613</td><td>52244531</td></tr><tr><td>20131011</td><td>82674757</td><td>27666473</td></tr><tr><td>20131012</td><td>81901136</td><td>31322983</td></tr><tr><td>20131013</td><td>71149981</td><td>31850475</td></tr><tr><td>20131014</td><td>130315300</td><td>40652625</td></tr><tr><td>20131015</td><td>85704304</td><td>35099198</td></tr><tr><td>20131016</td><td>100948091</td><td>51261982</td></tr><tr><td>20131017</td><td>87203217</td><td>23274764</td></tr><tr><td>20131018</td><td>98151450</td><td>15938355</td></tr><tr><td>20131019</td><td>53148443</td><td>26684697</td></tr><tr><td>20131020</td><td>47766681</td><td>50884342</td></tr><tr><td>20131021</td><td>106030290</td><td>45781403</td></tr><tr><td>20131022</td><td>145611496</td><td>96172537</td></tr><tr><td>20131023</td><td>106093112</td><td>73788462</td></tr><tr><td>20131024</td><td>69530830</td><td>58183921</td></tr><tr><td>20131025</td><td>69936453</td><td>52225802</td></tr><tr><td>20131026</td><td>55752506</td><td>49357211</td></tr><tr><td>20131027</td><td>68007543</td><td>46520570</td></tr><tr><td>20131028</td><td>129993375</td><td>55089464</td></tr><tr><td>20131029</td><td>102050144</td><td>43052468</td></tr><tr><td>20131030</td><td>109663260</td><td>63908701</td></tr><tr><td>20131031</td><td>90926701</td><td>55607833</td></tr><tr><td>20131101</td><td>150904945</td><td>66933119</td></tr><tr><td>20131102</td><td>62132300</td><td>26705506</td></tr><tr><td>20131103</td><td>68707870</td><td>30895754</td></tr><tr><td>20131104</td><td>300027403</td><td>130970051</td></tr><tr><td>20131105</td><td>180904814</td><td>82689864</td></tr><tr><td>20131106</td><td>181474910</td><td>131707820</td></tr><tr><td>20131107</td><td>167963464</td><td>83306452</td></tr><tr><td>20131108</td><td>231749471</td><td>92856307</td></tr><tr><td>20131109</td><td>126467028</td><td>45885771</td></tr><tr><td>20131110</td><td>164813471</td><td>85293644</td></tr><tr><td>20131111</td><td>147986439</td><td>227124951</td></tr><tr><td>20131112</td><td>148469647</td><td>131263524</td></tr><tr><td>20131113</td><td>174554976</td><td>79121440</td></tr><tr><td>20131114</td><td>167996058</td><td>52554150</td></tr><tr><td>20131115</td><td>124270704</td><td>58210254</td></tr><tr><td>20131116</td><td>118085705</td><td>28996272</td></tr><tr><td>20131117</td><td>111786622</td><td>42358155</td></tr><tr><td>20131118</td><td>195766477</td><td>136916782</td></tr><tr><td>20131119</td><td>131365419</td><td>70308235</td></tr><tr><td>20131120</td><td>166163002</td><td>86951218</td></tr><tr><td>20131121</td><td>221166938</td><td>74733962</td></tr><tr><td>20131122</td><td>128528687</td><td>82717921</td></tr><tr><td>20131123</td><td>89109018</td><td>48882131</td></tr><tr><td>20131124</td><td>102394991</td><td>85555947</td></tr><tr><td>20131125</td><td>147143602</td><td>70113127</td></tr><tr><td>20131126</td><td>258037702</td><td>65802983</td></tr><tr><td>20131127</td><td>134774128</td><td>86129498</td></tr><tr><td>20131128</td><td>139760425</td><td>61453591</td></tr><tr><td>20131129</td><td>122536344</td><td>123982166</td></tr><tr><td>20131130</td><td>116865492</td><td>119660311</td></tr><tr><td>20131201</td><td>137889992</td><td>94632233</td></tr><tr><td>20131202</td><td>345313258</td><td>128465086</td></tr><tr><td>20131203</td><td>268666856</td><td>101662045</td></tr><tr><td>20131204</td><td>219456372</td><td>69981329</td></tr><tr><td>20131205</td><td>190825287</td><td>118256819</td></tr><tr><td>20131206</td><td>152197159</td><td>82625571</td></tr><tr><td>20131207</td><td>138882605</td><td>71916047</td></tr><tr><td>20131208</td><td>124886903</td><td>76314292</td></tr><tr><td>20131209</td><td>174652812</td><td>133355299</td></tr><tr><td>20131210</td><td>179471612</td><td>147147341</td></tr><tr><td>20131211</td><td>239916977</td><td>110314592</td></tr><tr><td>20131212</td><td>140360007</td><td>92090275</td></tr><tr><td>20131213</td><td>164672690</td><td>90778214</td></tr><tr><td>20131214</td><td>129045596</td><td>52234651</td></tr><tr><td>20131215</td><td>205882387</td><td>54757146</td></tr><tr><td>20131216</td><td>196489931</td><td>156157326</td></tr><tr><td>20131217</td><td>193980797</td><td>116537843</td></tr><tr><td>20131218</td><td>180215804</td><td>202220872</td></tr><tr><td>20131219</td><td>233631357</td><td>164023344</td></tr><tr><td>20131220</td><td>133867400</td><td>153678963</td></tr><tr><td>20131221</td><td>112000779</td><td>83151594</td></tr><tr><td>20131222</td><td>154543217</td><td>83998249</td></tr><tr><td>20131223</td><td>198747367</td><td>138478245</td></tr><tr><td>20131224</td><td>305406334</td><td>118607911</td></tr><tr><td>20131225</td><td>380076148</td><td>172755480</td></tr><tr><td>20131226</td><td>220287409</td><td>241852564</td></tr><tr><td>20131227</td><td>211118967</td><td>163522548</td></tr><tr><td>20131228</td><td>192808006</td><td>61383220</td></tr><tr><td>20131229</td><td>161473347</td><td>55129989</td></tr><tr><td>20131230</td><td>286358778</td><td>179826624</td></tr><tr><td>20131231</td><td>265851934</td><td>134232530</td></tr><tr><td>20140101</td><td>330926565</td><td>77367755</td></tr><tr><td>20140102</td><td>434956739</td><td>190155450</td></tr><tr><td>20140103</td><td>342074805</td><td>127714255</td></tr><tr><td>20140104</td><td>186085910</td><td>99869074</td></tr><tr><td>20140105</td><td>206030707</td><td>156781996</td></tr><tr><td>20140106</td><td>442494042</td><td>190917629</td></tr><tr><td>20140107</td><td>589726496</td><td>137972793</td></tr><tr><td>20140108</td><td>264025160</td><td>213880074</td></tr><tr><td>20140109</td><td>280698487</td><td>140391237</td></tr><tr><td>20140110</td><td>237797636</td><td>117259153</td></tr><tr><td>20140111</td><td>243403438</td><td>71530182</td></tr><tr><td>20140112</td><td>228255344</td><td>141505812</td></tr><tr><td>20140113</td><td>447207050</td><td>178923772</td></tr><tr><td>20140114</td><td>356907128</td><td>159778389</td></tr><tr><td>20140115</td><td>391408718</td><td>177618247</td></tr><tr><td>20140116</td><td>374947083</td><td>153167426</td></tr><tr><td>20140117</td><td>345426853</td><td>127385210</td></tr><tr><td>20140118</td><td>233200688</td><td>142869842</td></tr><tr><td>20140119</td><td>185082022</td><td>117629351</td></tr><tr><td>20140120</td><td>335761027</td><td>442996084</td></tr><tr><td>20140121</td><td>412854611</td><td>302825136</td></tr><tr><td>20140122</td><td>498730606</td><td>292856188</td></tr><tr><td>20140123</td><td>481450097</td><td>209024328</td></tr><tr><td>20140124</td><td>614103894</td><td>345829001</td></tr><tr><td>20140125</td><td>440854623</td><td>185524856</td></tr><tr><td>20140126</td><td>528402520</td><td>331752519</td></tr><tr><td>20140127</td><td>657199484</td><td>280645861</td></tr><tr><td>20140128</td><td>843424742</td><td>248334316</td></tr><tr><td>20140129</td><td>952479658</td><td>215164666</td></tr><tr><td>20140130</td><td>227916211</td><td>105288105</td></tr><tr><td>20140131</td><td>87324175</td><td>48132389</td></tr><tr><td>20140201</td><td>64287172</td><td>61527060</td></tr><tr><td>20140202</td><td>57912761</td><td>24395720</td></tr><tr><td>20140203</td><td>84329848</td><td>46109194</td></tr><tr><td>20140204</td><td>83653646</td><td>44927333</td></tr><tr><td>20140205</td><td>209043990</td><td>95407704</td></tr><tr><td>20140206</td><td>227860276</td><td>111635336</td></tr><tr><td>20140207</td><td>578818221</td><td>273482213</td></tr><tr><td>20140208</td><td>358255468</td><td>183432237</td></tr><tr><td>20140209</td><td>341911234</td><td>262954049</td></tr><tr><td>20140210</td><td>647465140</td><td>431753411</td></tr><tr><td>20140211</td><td>818803205</td><td>243058162</td></tr><tr><td>20140212</td><td>763009770</td><td>354585919</td></tr><tr><td>20140213</td><td>626698794</td><td>362757986</td></tr><tr><td>20140214</td><td>490710434</td><td>263261899</td></tr><tr><td>20140215</td><td>255393179</td><td>121463693</td></tr><tr><td>20140216</td><td>331324628</td><td>183057224</td></tr><tr><td>20140217</td><td>706118060</td><td>405809267</td></tr><tr><td>20140218</td><td>593563145</td><td>271125324</td></tr><tr><td>20140219</td><td>408367966</td><td>323990451</td></tr><tr><td>20140220</td><td>478925191</td><td>230855410</td></tr><tr><td>20140221</td><td>353981687</td><td>178914448</td></tr><tr><td>20140222</td><td>282022706</td><td>134735118</td></tr><tr><td>20140223</td><td>337053711</td><td>195214052</td></tr><tr><td>20140224</td><td>656317045</td><td>473470156</td></tr><tr><td>20140225</td><td>563505889</td><td>299155352</td></tr><tr><td>20140226</td><td>680801145</td><td>284819682</td></tr><tr><td>20140227</td><td>672909288</td><td>492786036</td></tr><tr><td>20140228</td><td>428721754</td><td>322030204</td></tr><tr><td>20140301</td><td>362865580</td><td>211279011</td></tr><tr><td>20140302</td><td>276202230</td><td>246199417</td></tr><tr><td>20140303</td><td>505305862</td><td>513017360</td></tr><tr><td>20140304</td><td>524146340</td><td>250562978</td></tr><tr><td>20140305</td><td>454295491</td><td>209072753</td></tr><tr><td>20140306</td><td>561787770</td><td>243149884</td></tr><tr><td>20140307</td><td>380139779</td><td>291087220</td></tr><tr><td>20140308</td><td>243274169</td><td>140323202</td></tr><tr><td>20140309</td><td>244752519</td><td>206312503</td></tr><tr><td>20140310</td><td>497338076</td><td>308040624</td></tr><tr><td>20140311</td><td>430500816</td><td>496039886</td></tr><tr><td>20140312</td><td>377007897</td><td>416491268</td></tr><tr><td>20140313</td><td>346286910</td><td>316973542</td></tr><tr><td>20140314</td><td>315897431</td><td>311575572</td></tr><tr><td>20140315</td><td>287407002</td><td>242799048</td></tr><tr><td>20140316</td><td>269387391</td><td>390584547</td></tr><tr><td>20140317</td><td>339008082</td><td>440624592</td></tr><tr><td>20140318</td><td>435479117</td><td>410240726</td></tr><tr><td>20140319</td><td>359014064</td><td>429298917</td></tr><tr><td>20140320</td><td>365011495</td><td>336076380</td></tr><tr><td>20140321</td><td>282351818</td><td>259655286</td></tr><tr><td>20140322</td><td>191700135</td><td>138039412</td></tr><tr><td>20140323</td><td>167456369</td><td>186443311</td></tr><tr><td>20140324</td><td>313180334</td><td>437825259</td></tr><tr><td>20140325</td><td>314345006</td><td>312710515</td></tr><tr><td>20140326</td><td>272935544</td><td>450254233</td></tr><tr><td>20140327</td><td>266231082</td><td>359071642</td></tr><tr><td>20140328</td><td>225966355</td><td>405443946</td></tr><tr><td>20140329</td><td>160250985</td><td>155006056</td></tr><tr><td>20140330</td><td>205533934</td><td>264714811</td></tr><tr><td>20140331</td><td>398884905</td><td>423852634</td></tr><tr><td>20140401</td><td>453320585</td><td>277429358</td></tr><tr><td>20140402</td><td>355347118</td><td>272612066</td></tr><tr><td>20140403</td><td>363877120</td><td>266605457</td></tr><tr><td>20140404</td><td>251895894</td><td>200192637</td></tr><tr><td>20140405</td><td>202336542</td><td>163199682</td></tr><tr><td>20140406</td><td>129477254</td><td>139576683</td></tr><tr><td>20140407</td><td>196936223</td><td>176966561</td></tr><tr><td>20140408</td><td>354770149</td><td>250015131</td></tr><tr><td>20140409</td><td>383347565</td><td>289330278</td></tr><tr><td>20140410</td><td>386567460</td><td>286914864</td></tr><tr><td>20140411</td><td>237829882</td><td>277077434</td></tr><tr><td>20140412</td><td>177642053</td><td>123295320</td></tr><tr><td>20140413</td><td>208172985</td><td>178934722</td></tr><tr><td>20140414</td><td>309853269</td><td>415986984</td></tr><tr><td>20140415</td><td>428681231</td><td>285293076</td></tr><tr><td>20140416</td><td>387847838</td><td>255914640</td></tr><tr><td>20140417</td><td>355792647</td><td>265341592</td></tr><tr><td>20140418</td><td>239300383</td><td>225952909</td></tr><tr><td>20140419</td><td>268729366</td><td>146374940</td></tr><tr><td>20140420</td><td>191259529</td><td>161057781</td></tr><tr><td>20140421</td><td>301134667</td><td>295635256</td></tr><tr><td>20140422</td><td>285248757</td><td>268810141</td></tr><tr><td>20140423</td><td>313677307</td><td>278470936</td></tr><tr><td>20140424</td><td>318358891</td><td>224536754</td></tr><tr><td>20140425</td><td>220927432</td><td>227764292</td></tr><tr><td>20140426</td><td>151625415</td><td>158122962</td></tr><tr><td>20140427</td><td>146837951</td><td>191915377</td></tr><tr><td>20140428</td><td>324937272</td><td>327724735</td></tr><tr><td>20140429</td><td>330607104</td><td>307578349</td></tr><tr><td>20140430</td><td>260091330</td><td>281835975</td></tr><tr><td>20140501</td><td>193045106</td><td>143362755</td></tr><tr><td>20140502</td><td>125336258</td><td>121222064</td></tr><tr><td>20140503</td><td>185094488</td><td>199568043</td></tr><tr><td>20140504</td><td>303087562</td><td>413222034</td></tr><tr><td>20140505</td><td>370924149</td><td>309330781</td></tr><tr><td>20140506</td><td>318002728</td><td>341108696</td></tr><tr><td>20140507</td><td>417327518</td><td>239372999</td></tr><tr><td>20140508</td><td>392838756</td><td>273187499</td></tr><tr><td>20140509</td><td>281479009</td><td>247743971</td></tr><tr><td>20140510</td><td>287240171</td><td>147248328</td></tr><tr><td>20140511</td><td>182424063</td><td>152945581</td></tr><tr><td>20140512</td><td>325108597</td><td>293952908</td></tr><tr><td>20140513</td><td>275241493</td><td>257918375</td></tr><tr><td>20140514</td><td>305474522</td><td>316225442</td></tr><tr><td>20140515</td><td>313367089</td><td>238307643</td></tr><tr><td>20140516</td><td>231967423</td><td>282094916</td></tr><tr><td>20140517</td><td>170983868</td><td>145854372</td></tr><tr><td>20140518</td><td>164419642</td><td>153440019</td></tr><tr><td>20140519</td><td>259077930</td><td>293791406</td></tr><tr><td>20140520</td><td>453955303</td><td>260040720</td></tr><tr><td>20140521</td><td>297799722</td><td>250223726</td></tr><tr><td>20140522</td><td>344636549</td><td>251108485</td></tr><tr><td>20140523</td><td>249546195</td><td>229000787</td></tr><tr><td>20140524</td><td>160073254</td><td>154409868</td></tr><tr><td>20140525</td><td>166943526</td><td>231004758</td></tr><tr><td>20140526</td><td>344890868</td><td>274707572</td></tr><tr><td>20140527</td><td>299049555</td><td>321965845</td></tr><tr><td>20140528</td><td>276134813</td><td>415891684</td></tr><tr><td>20140529</td><td>320549863</td><td>313827800</td></tr><tr><td>20140530</td><td>226547701</td><td>312802179</td></tr><tr><td>20140531</td><td>146823669</td><td>142862063</td></tr><tr><td>20140601</td><td>183489775</td><td>149829253</td></tr><tr><td>20140602</td><td>158219402</td><td>170409506</td></tr><tr><td>20140603</td><td>270887462</td><td>385622582</td></tr><tr><td>20140604</td><td>274460744</td><td>303978838</td></tr><tr><td>20140605</td><td>380042567</td><td>355645445</td></tr><tr><td>20140606</td><td>301413900</td><td>274862067</td></tr><tr><td>20140607</td><td>187801995</td><td>146203577</td></tr><tr><td>20140608</td><td>302171269</td><td>169525332</td></tr><tr><td>20140609</td><td>366114374</td><td>287520152</td></tr><tr><td>20140610</td><td>354031597</td><td>298190025</td></tr><tr><td>20140611</td><td>327661453</td><td>246127540</td></tr><tr><td>20140612</td><td>332365185</td><td>236467885</td></tr><tr><td>20140613</td><td>216923770</td><td>386799040</td></tr><tr><td>20140614</td><td>181574530</td><td>229916191</td></tr><tr><td>20140615</td><td>166080126</td><td>116623756</td></tr><tr><td>20140616</td><td>387308484</td><td>492349489</td></tr><tr><td>20140617</td><td>270350693</td><td>502560223</td></tr><tr><td>20140618</td><td>335262709</td><td>421920230</td></tr><tr><td>20140619</td><td>338609087</td><td>284956260</td></tr><tr><td>20140620</td><td>251582530</td><td>286583065</td></tr><tr><td>20140621</td><td>177999186</td><td>231003775</td></tr><tr><td>20140622</td><td>147180819</td><td>361285652</td></tr><tr><td>20140623</td><td>232227670</td><td>373779624</td></tr><tr><td>20140624</td><td>245450766</td><td>428471509</td></tr><tr><td>20140625</td><td>264663201</td><td>547295931</td></tr><tr><td>20140626</td><td>297628039</td><td>418742109</td></tr><tr><td>20140627</td><td>264282703</td><td>399444352</td></tr><tr><td>20140628</td><td>153826161</td><td>283400236</td></tr><tr><td>20140629</td><td>158801540</td><td>261170799</td></tr><tr><td>20140630</td><td>334054112</td><td>456547794</td></tr><tr><td>20140701</td><td>384428753</td><td>374164541</td></tr><tr><td>20140702</td><td>384555819</td><td>328950951</td></tr><tr><td>20140703</td><td>297894643</td><td>289009780</td></tr><tr><td>20140704</td><td>211649838</td><td>264494550</td></tr><tr><td>20140705</td><td>169383796</td><td>272535138</td></tr><tr><td>20140706</td><td>199569025</td><td>195530758</td></tr><tr><td>20140707</td><td>272182847</td><td>317612569</td></tr><tr><td>20140708</td><td>224240103</td><td>340453063</td></tr><tr><td>20140709</td><td>278005555</td><td>269642881</td></tr><tr><td>20140710</td><td>283095921</td><td>326009240</td></tr><tr><td>20140711</td><td>208671021</td><td>240050748</td></tr><tr><td>20140712</td><td>177644343</td><td>149081488</td></tr><tr><td>20140713</td><td>179759885</td><td>199459990</td></tr><tr><td>20140714</td><td>254797524</td><td>284753279</td></tr><tr><td>20140715</td><td>334810012</td><td>261722182</td></tr><tr><td>20140716</td><td>394890140</td><td>234775948</td></tr><tr><td>20140717</td><td>253011280</td><td>298279385</td></tr><tr><td>20140718</td><td>208959595</td><td>208671287</td></tr><tr><td>20140719</td><td>210318023</td><td>155464283</td></tr><tr><td>20140720</td><td>176449304</td><td>174462836</td></tr><tr><td>20140721</td><td>378088594</td><td>434191479</td></tr><tr><td>20140722</td><td>243084133</td><td>369043423</td></tr><tr><td>20140723</td><td>265461894</td><td>308353077</td></tr><tr><td>20140724</td><td>277044480</td><td>347622431</td></tr><tr><td>20140725</td><td>181641088</td><td>262874791</td></tr><tr><td>20140726</td><td>128268053</td><td>282653341</td></tr><tr><td>20140727</td><td>151406251</td><td>166610652</td></tr><tr><td>20140728</td><td>371762756</td><td>345986909</td></tr><tr><td>20140729</td><td>228093046</td><td>303480103</td></tr><tr><td>20140730</td><td>209917272</td><td>250117716</td></tr><tr><td>20140731</td><td>191728916</td><td>277194379</td></tr><tr><td>20140801</td><td>374884735</td><td>252540858</td></tr><tr><td>20140802</td><td>189092130</td><td>172250225</td></tr><tr><td>20140803</td><td>173825397</td><td>127125217</td></tr><tr><td>20140804</td><td>330640884</td><td>322907524</td></tr><tr><td>20140805</td><td>394780870</td><td>221706539</td></tr><tr><td>20140806</td><td>288821016</td><td>282346594</td></tr><tr><td>20140807</td><td>247646474</td><td>253659514</td></tr><tr><td>20140808</td><td>233903717</td><td>311648757</td></tr><tr><td>20140809</td><td>160262764</td><td>163611708</td></tr><tr><td>20140810</td><td>259534870</td><td>189909225</td></tr><tr><td>20140811</td><td>331550471</td><td>418603336</td></tr><tr><td>20140812</td><td>258493673</td><td>309754858</td></tr><tr><td>20140813</td><td>261506619</td><td>303975517</td></tr><tr><td>20140814</td><td>257702660</td><td>211939431</td></tr><tr><td>20140815</td><td>244551620</td><td>236516007</td></tr><tr><td>20140816</td><td>215059736</td><td>219214339</td></tr><tr><td>20140817</td><td>149978271</td><td>139564084</td></tr><tr><td>20140818</td><td>298499146</td><td>259169016</td></tr><tr><td>20140819</td><td>266401973</td><td>254929877</td></tr><tr><td>20140820</td><td>308378692</td><td>202452782</td></tr><tr><td>20140821</td><td>251763517</td><td>219963356</td></tr><tr><td>20140822</td><td>246316056</td><td>179349206</td></tr><tr><td>20140823</td><td>141412027</td><td>199377531</td></tr><tr><td>20140824</td><td>130195484</td><td>191080151</td></tr><tr><td>20140825</td><td>309574223</td><td>312413411</td></tr><tr><td>20140826</td><td>306945089</td><td>285478563</td></tr><tr><td>20140827</td><td>302194801</td><td>468164147</td></tr><tr><td>20140828</td><td>245082751</td><td>297893861</td></tr><tr><td>20140829</td><td>267554713</td><td>273756380</td></tr><tr><td>20140830</td><td>199708772</td><td>196374134</td></tr><tr><td>20140831</td><td>275090213</td><td>292943033</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         "20130701",
         "32488348",
         "5525022"
        ],
        [
         "20130702",
         "29037390",
         "2554548"
        ],
        [
         "20130703",
         "27270770",
         "5953867"
        ],
        [
         "20130704",
         "18321185",
         "6410729"
        ],
        [
         "20130705",
         "11648749",
         "2763587"
        ],
        [
         "20130706",
         "36751272",
         "1616635"
        ],
        [
         "20130707",
         "8962232",
         "3982735"
        ],
        [
         "20130708",
         "57258266",
         "8347729"
        ],
        [
         "20130709",
         "26798941",
         "3473059"
        ],
        [
         "20130710",
         "30696506",
         "2597169"
        ],
        [
         "20130711",
         "44075197",
         "3508800"
        ],
        [
         "20130712",
         "34183904",
         "8492573"
        ],
        [
         "20130713",
         "15164717",
         "3482829"
        ],
        [
         "20130714",
         "22615303",
         "2784107"
        ],
        [
         "20130715",
         "48128555",
         "13107943"
        ],
        [
         "20130716",
         "50622847",
         "11864981"
        ],
        [
         "20130717",
         "29015682",
         "10911513"
        ],
        [
         "20130718",
         "24234505",
         "11765356"
        ],
        [
         "20130719",
         "33680124",
         "9244769"
        ],
        [
         "20130720",
         "20439079",
         "4601143"
        ],
        [
         "20130721",
         "21142394",
         "2681331"
        ],
        [
         "20130722",
         "40448896",
         "19144267"
        ],
        [
         "20130723",
         "58136147",
         "24404051"
        ],
        [
         "20130724",
         "48422518",
         "36258592"
        ],
        [
         "20130725",
         "57433418",
         "38212836"
        ],
        [
         "20130726",
         "44721817",
         "39192369"
        ],
        [
         "20130727",
         "17194451",
         "15058893"
        ],
        [
         "20130728",
         "36255382",
         "7683211"
        ],
        [
         "20130729",
         "53512076",
         "18599364"
        ],
        [
         "20130730",
         "47481243",
         "13048582"
        ],
        [
         "20130731",
         "54569637",
         "9534040"
        ],
        [
         "20130801",
         "53369962",
         "18864468"
        ],
        [
         "20130802",
         "38064536",
         "40150769"
        ],
        [
         "20130803",
         "21595789",
         "20701797"
        ],
        [
         "20130804",
         "45745254",
         "8263965"
        ],
        [
         "20130805",
         "43632203",
         "15797507"
        ],
        [
         "20130806",
         "50866184",
         "16401387"
        ],
        [
         "20130807",
         "43908081",
         "29708706"
        ],
        [
         "20130808",
         "44493490",
         "29551691"
        ],
        [
         "20130809",
         "33425186",
         "30131015"
        ],
        [
         "20130810",
         "20615307",
         "26614408"
        ],
        [
         "20130811",
         "55519543",
         "15372680"
        ],
        [
         "20130812",
         "94520502",
         "28586669"
        ],
        [
         "20130813",
         "48720919",
         "28790328"
        ],
        [
         "20130814",
         "30541167",
         "15031683"
        ],
        [
         "20130815",
         "42421222",
         "52659327"
        ],
        [
         "20130816",
         "41683536",
         "33739844"
        ],
        [
         "20130817",
         "17670519",
         "4674983"
        ],
        [
         "20130818",
         "59703092",
         "15218300"
        ],
        [
         "20130819",
         "109184209",
         "28339260"
        ],
        [
         "20130820",
         "53675509",
         "30131225"
        ],
        [
         "20130821",
         "38184446",
         "29983342"
        ],
        [
         "20130822",
         "89130737",
         "25000672"
        ],
        [
         "20130823",
         "37878575",
         "25680323"
        ],
        [
         "20130824",
         "44367268",
         "14316598"
        ],
        [
         "20130825",
         "43306288",
         "18117808"
        ],
        [
         "20130826",
         "74496541",
         "20637986"
        ],
        [
         "20130827",
         "45268028",
         "60137633"
        ],
        [
         "20130828",
         "46602319",
         "33696861"
        ],
        [
         "20130829",
         "47518666",
         "35944968"
        ],
        [
         "20130830",
         "56488844",
         "20630752"
        ],
        [
         "20130831",
         "47655303",
         "22012016"
        ],
        [
         "20130901",
         "56239802",
         "59339949"
        ],
        [
         "20130902",
         "140844739",
         "17785524"
        ],
        [
         "20130903",
         "80507880",
         "39328144"
        ],
        [
         "20130904",
         "33366708",
         "27452600"
        ],
        [
         "20130905",
         "38716505",
         "21800904"
        ],
        [
         "20130906",
         "38923186",
         "33417903"
        ],
        [
         "20130907",
         "34076183",
         "20344424"
        ],
        [
         "20130908",
         "38796909",
         "14919122"
        ],
        [
         "20130909",
         "49312473",
         "45621186"
        ],
        [
         "20130910",
         "94684143",
         "37128363"
        ],
        [
         "20130911",
         "98944459",
         "71674082"
        ],
        [
         "20130912",
         "68573684",
         "45147220"
        ],
        [
         "20130913",
         "71655946",
         "60512675"
        ],
        [
         "20130914",
         "30975443",
         "36312660"
        ],
        [
         "20130915",
         "37059683",
         "25020715"
        ],
        [
         "20130916",
         "161656210",
         "45184589"
        ],
        [
         "20130917",
         "76204815",
         "58260798"
        ],
        [
         "20130918",
         "42926608",
         "47203221"
        ],
        [
         "20130919",
         "24778048",
         "11418512"
        ],
        [
         "20130920",
         "28365762",
         "15188254"
        ],
        [
         "20130921",
         "32406340",
         "26596179"
        ],
        [
         "20130922",
         "67514169",
         "47962055"
        ],
        [
         "20130923",
         "54658160",
         "58285879"
        ],
        [
         "20130924",
         "74250859",
         "56680792"
        ],
        [
         "20130925",
         "47274168",
         "74838162"
        ],
        [
         "20130926",
         "68718693",
         "48467529"
        ],
        [
         "20130927",
         "42166121",
         "27639603"
        ],
        [
         "20130928",
         "39995798",
         "58105720"
        ],
        [
         "20130929",
         "34433848",
         "40295202"
        ],
        [
         "20130930",
         "28398050",
         "30181353"
        ],
        [
         "20131001",
         "19137499",
         "12813267"
        ],
        [
         "20131002",
         "11562708",
         "7233874"
        ],
        [
         "20131003",
         "10101194",
         "6223263"
        ],
        [
         "20131004",
         "19907689",
         "23412350"
        ],
        [
         "20131005",
         "26364686",
         "13966453"
        ],
        [
         "20131006",
         "38730653",
         "13464528"
        ],
        [
         "20131007",
         "42797733",
         "28894400"
        ],
        [
         "20131008",
         "109806912",
         "77856096"
        ],
        [
         "20131009",
         "72422299",
         "33999376"
        ],
        [
         "20131010",
         "72094613",
         "52244531"
        ],
        [
         "20131011",
         "82674757",
         "27666473"
        ],
        [
         "20131012",
         "81901136",
         "31322983"
        ],
        [
         "20131013",
         "71149981",
         "31850475"
        ],
        [
         "20131014",
         "130315300",
         "40652625"
        ],
        [
         "20131015",
         "85704304",
         "35099198"
        ],
        [
         "20131016",
         "100948091",
         "51261982"
        ],
        [
         "20131017",
         "87203217",
         "23274764"
        ],
        [
         "20131018",
         "98151450",
         "15938355"
        ],
        [
         "20131019",
         "53148443",
         "26684697"
        ],
        [
         "20131020",
         "47766681",
         "50884342"
        ],
        [
         "20131021",
         "106030290",
         "45781403"
        ],
        [
         "20131022",
         "145611496",
         "96172537"
        ],
        [
         "20131023",
         "106093112",
         "73788462"
        ],
        [
         "20131024",
         "69530830",
         "58183921"
        ],
        [
         "20131025",
         "69936453",
         "52225802"
        ],
        [
         "20131026",
         "55752506",
         "49357211"
        ],
        [
         "20131027",
         "68007543",
         "46520570"
        ],
        [
         "20131028",
         "129993375",
         "55089464"
        ],
        [
         "20131029",
         "102050144",
         "43052468"
        ],
        [
         "20131030",
         "109663260",
         "63908701"
        ],
        [
         "20131031",
         "90926701",
         "55607833"
        ],
        [
         "20131101",
         "150904945",
         "66933119"
        ],
        [
         "20131102",
         "62132300",
         "26705506"
        ],
        [
         "20131103",
         "68707870",
         "30895754"
        ],
        [
         "20131104",
         "300027403",
         "130970051"
        ],
        [
         "20131105",
         "180904814",
         "82689864"
        ],
        [
         "20131106",
         "181474910",
         "131707820"
        ],
        [
         "20131107",
         "167963464",
         "83306452"
        ],
        [
         "20131108",
         "231749471",
         "92856307"
        ],
        [
         "20131109",
         "126467028",
         "45885771"
        ],
        [
         "20131110",
         "164813471",
         "85293644"
        ],
        [
         "20131111",
         "147986439",
         "227124951"
        ],
        [
         "20131112",
         "148469647",
         "131263524"
        ],
        [
         "20131113",
         "174554976",
         "79121440"
        ],
        [
         "20131114",
         "167996058",
         "52554150"
        ],
        [
         "20131115",
         "124270704",
         "58210254"
        ],
        [
         "20131116",
         "118085705",
         "28996272"
        ],
        [
         "20131117",
         "111786622",
         "42358155"
        ],
        [
         "20131118",
         "195766477",
         "136916782"
        ],
        [
         "20131119",
         "131365419",
         "70308235"
        ],
        [
         "20131120",
         "166163002",
         "86951218"
        ],
        [
         "20131121",
         "221166938",
         "74733962"
        ],
        [
         "20131122",
         "128528687",
         "82717921"
        ],
        [
         "20131123",
         "89109018",
         "48882131"
        ],
        [
         "20131124",
         "102394991",
         "85555947"
        ],
        [
         "20131125",
         "147143602",
         "70113127"
        ],
        [
         "20131126",
         "258037702",
         "65802983"
        ],
        [
         "20131127",
         "134774128",
         "86129498"
        ],
        [
         "20131128",
         "139760425",
         "61453591"
        ],
        [
         "20131129",
         "122536344",
         "123982166"
        ],
        [
         "20131130",
         "116865492",
         "119660311"
        ],
        [
         "20131201",
         "137889992",
         "94632233"
        ],
        [
         "20131202",
         "345313258",
         "128465086"
        ],
        [
         "20131203",
         "268666856",
         "101662045"
        ],
        [
         "20131204",
         "219456372",
         "69981329"
        ],
        [
         "20131205",
         "190825287",
         "118256819"
        ],
        [
         "20131206",
         "152197159",
         "82625571"
        ],
        [
         "20131207",
         "138882605",
         "71916047"
        ],
        [
         "20131208",
         "124886903",
         "76314292"
        ],
        [
         "20131209",
         "174652812",
         "133355299"
        ],
        [
         "20131210",
         "179471612",
         "147147341"
        ],
        [
         "20131211",
         "239916977",
         "110314592"
        ],
        [
         "20131212",
         "140360007",
         "92090275"
        ],
        [
         "20131213",
         "164672690",
         "90778214"
        ],
        [
         "20131214",
         "129045596",
         "52234651"
        ],
        [
         "20131215",
         "205882387",
         "54757146"
        ],
        [
         "20131216",
         "196489931",
         "156157326"
        ],
        [
         "20131217",
         "193980797",
         "116537843"
        ],
        [
         "20131218",
         "180215804",
         "202220872"
        ],
        [
         "20131219",
         "233631357",
         "164023344"
        ],
        [
         "20131220",
         "133867400",
         "153678963"
        ],
        [
         "20131221",
         "112000779",
         "83151594"
        ],
        [
         "20131222",
         "154543217",
         "83998249"
        ],
        [
         "20131223",
         "198747367",
         "138478245"
        ],
        [
         "20131224",
         "305406334",
         "118607911"
        ],
        [
         "20131225",
         "380076148",
         "172755480"
        ],
        [
         "20131226",
         "220287409",
         "241852564"
        ],
        [
         "20131227",
         "211118967",
         "163522548"
        ],
        [
         "20131228",
         "192808006",
         "61383220"
        ],
        [
         "20131229",
         "161473347",
         "55129989"
        ],
        [
         "20131230",
         "286358778",
         "179826624"
        ],
        [
         "20131231",
         "265851934",
         "134232530"
        ],
        [
         "20140101",
         "330926565",
         "77367755"
        ],
        [
         "20140102",
         "434956739",
         "190155450"
        ],
        [
         "20140103",
         "342074805",
         "127714255"
        ],
        [
         "20140104",
         "186085910",
         "99869074"
        ],
        [
         "20140105",
         "206030707",
         "156781996"
        ],
        [
         "20140106",
         "442494042",
         "190917629"
        ],
        [
         "20140107",
         "589726496",
         "137972793"
        ],
        [
         "20140108",
         "264025160",
         "213880074"
        ],
        [
         "20140109",
         "280698487",
         "140391237"
        ],
        [
         "20140110",
         "237797636",
         "117259153"
        ],
        [
         "20140111",
         "243403438",
         "71530182"
        ],
        [
         "20140112",
         "228255344",
         "141505812"
        ],
        [
         "20140113",
         "447207050",
         "178923772"
        ],
        [
         "20140114",
         "356907128",
         "159778389"
        ],
        [
         "20140115",
         "391408718",
         "177618247"
        ],
        [
         "20140116",
         "374947083",
         "153167426"
        ],
        [
         "20140117",
         "345426853",
         "127385210"
        ],
        [
         "20140118",
         "233200688",
         "142869842"
        ],
        [
         "20140119",
         "185082022",
         "117629351"
        ],
        [
         "20140120",
         "335761027",
         "442996084"
        ],
        [
         "20140121",
         "412854611",
         "302825136"
        ],
        [
         "20140122",
         "498730606",
         "292856188"
        ],
        [
         "20140123",
         "481450097",
         "209024328"
        ],
        [
         "20140124",
         "614103894",
         "345829001"
        ],
        [
         "20140125",
         "440854623",
         "185524856"
        ],
        [
         "20140126",
         "528402520",
         "331752519"
        ],
        [
         "20140127",
         "657199484",
         "280645861"
        ],
        [
         "20140128",
         "843424742",
         "248334316"
        ],
        [
         "20140129",
         "952479658",
         "215164666"
        ],
        [
         "20140130",
         "227916211",
         "105288105"
        ],
        [
         "20140131",
         "87324175",
         "48132389"
        ],
        [
         "20140201",
         "64287172",
         "61527060"
        ],
        [
         "20140202",
         "57912761",
         "24395720"
        ],
        [
         "20140203",
         "84329848",
         "46109194"
        ],
        [
         "20140204",
         "83653646",
         "44927333"
        ],
        [
         "20140205",
         "209043990",
         "95407704"
        ],
        [
         "20140206",
         "227860276",
         "111635336"
        ],
        [
         "20140207",
         "578818221",
         "273482213"
        ],
        [
         "20140208",
         "358255468",
         "183432237"
        ],
        [
         "20140209",
         "341911234",
         "262954049"
        ],
        [
         "20140210",
         "647465140",
         "431753411"
        ],
        [
         "20140211",
         "818803205",
         "243058162"
        ],
        [
         "20140212",
         "763009770",
         "354585919"
        ],
        [
         "20140213",
         "626698794",
         "362757986"
        ],
        [
         "20140214",
         "490710434",
         "263261899"
        ],
        [
         "20140215",
         "255393179",
         "121463693"
        ],
        [
         "20140216",
         "331324628",
         "183057224"
        ],
        [
         "20140217",
         "706118060",
         "405809267"
        ],
        [
         "20140218",
         "593563145",
         "271125324"
        ],
        [
         "20140219",
         "408367966",
         "323990451"
        ],
        [
         "20140220",
         "478925191",
         "230855410"
        ],
        [
         "20140221",
         "353981687",
         "178914448"
        ],
        [
         "20140222",
         "282022706",
         "134735118"
        ],
        [
         "20140223",
         "337053711",
         "195214052"
        ],
        [
         "20140224",
         "656317045",
         "473470156"
        ],
        [
         "20140225",
         "563505889",
         "299155352"
        ],
        [
         "20140226",
         "680801145",
         "284819682"
        ],
        [
         "20140227",
         "672909288",
         "492786036"
        ],
        [
         "20140228",
         "428721754",
         "322030204"
        ],
        [
         "20140301",
         "362865580",
         "211279011"
        ],
        [
         "20140302",
         "276202230",
         "246199417"
        ],
        [
         "20140303",
         "505305862",
         "513017360"
        ],
        [
         "20140304",
         "524146340",
         "250562978"
        ],
        [
         "20140305",
         "454295491",
         "209072753"
        ],
        [
         "20140306",
         "561787770",
         "243149884"
        ],
        [
         "20140307",
         "380139779",
         "291087220"
        ],
        [
         "20140308",
         "243274169",
         "140323202"
        ],
        [
         "20140309",
         "244752519",
         "206312503"
        ],
        [
         "20140310",
         "497338076",
         "308040624"
        ],
        [
         "20140311",
         "430500816",
         "496039886"
        ],
        [
         "20140312",
         "377007897",
         "416491268"
        ],
        [
         "20140313",
         "346286910",
         "316973542"
        ],
        [
         "20140314",
         "315897431",
         "311575572"
        ],
        [
         "20140315",
         "287407002",
         "242799048"
        ],
        [
         "20140316",
         "269387391",
         "390584547"
        ],
        [
         "20140317",
         "339008082",
         "440624592"
        ],
        [
         "20140318",
         "435479117",
         "410240726"
        ],
        [
         "20140319",
         "359014064",
         "429298917"
        ],
        [
         "20140320",
         "365011495",
         "336076380"
        ],
        [
         "20140321",
         "282351818",
         "259655286"
        ],
        [
         "20140322",
         "191700135",
         "138039412"
        ],
        [
         "20140323",
         "167456369",
         "186443311"
        ],
        [
         "20140324",
         "313180334",
         "437825259"
        ],
        [
         "20140325",
         "314345006",
         "312710515"
        ],
        [
         "20140326",
         "272935544",
         "450254233"
        ],
        [
         "20140327",
         "266231082",
         "359071642"
        ],
        [
         "20140328",
         "225966355",
         "405443946"
        ],
        [
         "20140329",
         "160250985",
         "155006056"
        ],
        [
         "20140330",
         "205533934",
         "264714811"
        ],
        [
         "20140331",
         "398884905",
         "423852634"
        ],
        [
         "20140401",
         "453320585",
         "277429358"
        ],
        [
         "20140402",
         "355347118",
         "272612066"
        ],
        [
         "20140403",
         "363877120",
         "266605457"
        ],
        [
         "20140404",
         "251895894",
         "200192637"
        ],
        [
         "20140405",
         "202336542",
         "163199682"
        ],
        [
         "20140406",
         "129477254",
         "139576683"
        ],
        [
         "20140407",
         "196936223",
         "176966561"
        ],
        [
         "20140408",
         "354770149",
         "250015131"
        ],
        [
         "20140409",
         "383347565",
         "289330278"
        ],
        [
         "20140410",
         "386567460",
         "286914864"
        ],
        [
         "20140411",
         "237829882",
         "277077434"
        ],
        [
         "20140412",
         "177642053",
         "123295320"
        ],
        [
         "20140413",
         "208172985",
         "178934722"
        ],
        [
         "20140414",
         "309853269",
         "415986984"
        ],
        [
         "20140415",
         "428681231",
         "285293076"
        ],
        [
         "20140416",
         "387847838",
         "255914640"
        ],
        [
         "20140417",
         "355792647",
         "265341592"
        ],
        [
         "20140418",
         "239300383",
         "225952909"
        ],
        [
         "20140419",
         "268729366",
         "146374940"
        ],
        [
         "20140420",
         "191259529",
         "161057781"
        ],
        [
         "20140421",
         "301134667",
         "295635256"
        ],
        [
         "20140422",
         "285248757",
         "268810141"
        ],
        [
         "20140423",
         "313677307",
         "278470936"
        ],
        [
         "20140424",
         "318358891",
         "224536754"
        ],
        [
         "20140425",
         "220927432",
         "227764292"
        ],
        [
         "20140426",
         "151625415",
         "158122962"
        ],
        [
         "20140427",
         "146837951",
         "191915377"
        ],
        [
         "20140428",
         "324937272",
         "327724735"
        ],
        [
         "20140429",
         "330607104",
         "307578349"
        ],
        [
         "20140430",
         "260091330",
         "281835975"
        ],
        [
         "20140501",
         "193045106",
         "143362755"
        ],
        [
         "20140502",
         "125336258",
         "121222064"
        ],
        [
         "20140503",
         "185094488",
         "199568043"
        ],
        [
         "20140504",
         "303087562",
         "413222034"
        ],
        [
         "20140505",
         "370924149",
         "309330781"
        ],
        [
         "20140506",
         "318002728",
         "341108696"
        ],
        [
         "20140507",
         "417327518",
         "239372999"
        ],
        [
         "20140508",
         "392838756",
         "273187499"
        ],
        [
         "20140509",
         "281479009",
         "247743971"
        ],
        [
         "20140510",
         "287240171",
         "147248328"
        ],
        [
         "20140511",
         "182424063",
         "152945581"
        ],
        [
         "20140512",
         "325108597",
         "293952908"
        ],
        [
         "20140513",
         "275241493",
         "257918375"
        ],
        [
         "20140514",
         "305474522",
         "316225442"
        ],
        [
         "20140515",
         "313367089",
         "238307643"
        ],
        [
         "20140516",
         "231967423",
         "282094916"
        ],
        [
         "20140517",
         "170983868",
         "145854372"
        ],
        [
         "20140518",
         "164419642",
         "153440019"
        ],
        [
         "20140519",
         "259077930",
         "293791406"
        ],
        [
         "20140520",
         "453955303",
         "260040720"
        ],
        [
         "20140521",
         "297799722",
         "250223726"
        ],
        [
         "20140522",
         "344636549",
         "251108485"
        ],
        [
         "20140523",
         "249546195",
         "229000787"
        ],
        [
         "20140524",
         "160073254",
         "154409868"
        ],
        [
         "20140525",
         "166943526",
         "231004758"
        ],
        [
         "20140526",
         "344890868",
         "274707572"
        ],
        [
         "20140527",
         "299049555",
         "321965845"
        ],
        [
         "20140528",
         "276134813",
         "415891684"
        ],
        [
         "20140529",
         "320549863",
         "313827800"
        ],
        [
         "20140530",
         "226547701",
         "312802179"
        ],
        [
         "20140531",
         "146823669",
         "142862063"
        ],
        [
         "20140601",
         "183489775",
         "149829253"
        ],
        [
         "20140602",
         "158219402",
         "170409506"
        ],
        [
         "20140603",
         "270887462",
         "385622582"
        ],
        [
         "20140604",
         "274460744",
         "303978838"
        ],
        [
         "20140605",
         "380042567",
         "355645445"
        ],
        [
         "20140606",
         "301413900",
         "274862067"
        ],
        [
         "20140607",
         "187801995",
         "146203577"
        ],
        [
         "20140608",
         "302171269",
         "169525332"
        ],
        [
         "20140609",
         "366114374",
         "287520152"
        ],
        [
         "20140610",
         "354031597",
         "298190025"
        ],
        [
         "20140611",
         "327661453",
         "246127540"
        ],
        [
         "20140612",
         "332365185",
         "236467885"
        ],
        [
         "20140613",
         "216923770",
         "386799040"
        ],
        [
         "20140614",
         "181574530",
         "229916191"
        ],
        [
         "20140615",
         "166080126",
         "116623756"
        ],
        [
         "20140616",
         "387308484",
         "492349489"
        ],
        [
         "20140617",
         "270350693",
         "502560223"
        ],
        [
         "20140618",
         "335262709",
         "421920230"
        ],
        [
         "20140619",
         "338609087",
         "284956260"
        ],
        [
         "20140620",
         "251582530",
         "286583065"
        ],
        [
         "20140621",
         "177999186",
         "231003775"
        ],
        [
         "20140622",
         "147180819",
         "361285652"
        ],
        [
         "20140623",
         "232227670",
         "373779624"
        ],
        [
         "20140624",
         "245450766",
         "428471509"
        ],
        [
         "20140625",
         "264663201",
         "547295931"
        ],
        [
         "20140626",
         "297628039",
         "418742109"
        ],
        [
         "20140627",
         "264282703",
         "399444352"
        ],
        [
         "20140628",
         "153826161",
         "283400236"
        ],
        [
         "20140629",
         "158801540",
         "261170799"
        ],
        [
         "20140630",
         "334054112",
         "456547794"
        ],
        [
         "20140701",
         "384428753",
         "374164541"
        ],
        [
         "20140702",
         "384555819",
         "328950951"
        ],
        [
         "20140703",
         "297894643",
         "289009780"
        ],
        [
         "20140704",
         "211649838",
         "264494550"
        ],
        [
         "20140705",
         "169383796",
         "272535138"
        ],
        [
         "20140706",
         "199569025",
         "195530758"
        ],
        [
         "20140707",
         "272182847",
         "317612569"
        ],
        [
         "20140708",
         "224240103",
         "340453063"
        ],
        [
         "20140709",
         "278005555",
         "269642881"
        ],
        [
         "20140710",
         "283095921",
         "326009240"
        ],
        [
         "20140711",
         "208671021",
         "240050748"
        ],
        [
         "20140712",
         "177644343",
         "149081488"
        ],
        [
         "20140713",
         "179759885",
         "199459990"
        ],
        [
         "20140714",
         "254797524",
         "284753279"
        ],
        [
         "20140715",
         "334810012",
         "261722182"
        ],
        [
         "20140716",
         "394890140",
         "234775948"
        ],
        [
         "20140717",
         "253011280",
         "298279385"
        ],
        [
         "20140718",
         "208959595",
         "208671287"
        ],
        [
         "20140719",
         "210318023",
         "155464283"
        ],
        [
         "20140720",
         "176449304",
         "174462836"
        ],
        [
         "20140721",
         "378088594",
         "434191479"
        ],
        [
         "20140722",
         "243084133",
         "369043423"
        ],
        [
         "20140723",
         "265461894",
         "308353077"
        ],
        [
         "20140724",
         "277044480",
         "347622431"
        ],
        [
         "20140725",
         "181641088",
         "262874791"
        ],
        [
         "20140726",
         "128268053",
         "282653341"
        ],
        [
         "20140727",
         "151406251",
         "166610652"
        ],
        [
         "20140728",
         "371762756",
         "345986909"
        ],
        [
         "20140729",
         "228093046",
         "303480103"
        ],
        [
         "20140730",
         "209917272",
         "250117716"
        ],
        [
         "20140731",
         "191728916",
         "277194379"
        ],
        [
         "20140801",
         "374884735",
         "252540858"
        ],
        [
         "20140802",
         "189092130",
         "172250225"
        ],
        [
         "20140803",
         "173825397",
         "127125217"
        ],
        [
         "20140804",
         "330640884",
         "322907524"
        ],
        [
         "20140805",
         "394780870",
         "221706539"
        ],
        [
         "20140806",
         "288821016",
         "282346594"
        ],
        [
         "20140807",
         "247646474",
         "253659514"
        ],
        [
         "20140808",
         "233903717",
         "311648757"
        ],
        [
         "20140809",
         "160262764",
         "163611708"
        ],
        [
         "20140810",
         "259534870",
         "189909225"
        ],
        [
         "20140811",
         "331550471",
         "418603336"
        ],
        [
         "20140812",
         "258493673",
         "309754858"
        ],
        [
         "20140813",
         "261506619",
         "303975517"
        ],
        [
         "20140814",
         "257702660",
         "211939431"
        ],
        [
         "20140815",
         "244551620",
         "236516007"
        ],
        [
         "20140816",
         "215059736",
         "219214339"
        ],
        [
         "20140817",
         "149978271",
         "139564084"
        ],
        [
         "20140818",
         "298499146",
         "259169016"
        ],
        [
         "20140819",
         "266401973",
         "254929877"
        ],
        [
         "20140820",
         "308378692",
         "202452782"
        ],
        [
         "20140821",
         "251763517",
         "219963356"
        ],
        [
         "20140822",
         "246316056",
         "179349206"
        ],
        [
         "20140823",
         "141412027",
         "199377531"
        ],
        [
         "20140824",
         "130195484",
         "191080151"
        ],
        [
         "20140825",
         "309574223",
         "312413411"
        ],
        [
         "20140826",
         "306945089",
         "285478563"
        ],
        [
         "20140827",
         "302194801",
         "468164147"
        ],
        [
         "20140828",
         "245082751",
         "297893861"
        ],
        [
         "20140829",
         "267554713",
         "273756380"
        ],
        [
         "20140830",
         "199708772",
         "196374134"
        ],
        [
         "20140831",
         "275090213",
         "292943033"
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "_c0",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "_c1",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "_c2",
         "type": "\"string\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#读取任务1-1的输出文件，并下载（注意display方法是dataframe才有的，上面改为RDD实现后将打印结果复制出来即可）\n",
    "# task1_1_new_path=\"dbfs:/task1_1_new/part-00000-tid-4793263298214947573-d34bdb0c-51b7-4fce-8553-3c2135c28f2e-86-1-c000.csv\"\n",
    "\n",
    "# task1_1=spark.read.format(\"csv\").option(\"header\",\"false\").load(\"dbfs:/task1_1_new/part-00000-tid-4793263298214947573-d34bdb0c-51b7-4fce-8553-3c2135c28f2e-86-1-c000.csv\")\n",
    "\n",
    "# display(task1_1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "b8f98d26-f1f3-4476-a0e0-38677d524e66",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The total number of active users in August 2014 is: 12767\n"
     ]
    }
   ],
   "source": [
    "#任务1-2：统计活跃用户数量（用dataframe实现的，因此注释掉）\n",
    "\n",
    "# from pyspark.sql import functions as F\n",
    "# from pyspark.sql.window import Window\n",
    "\n",
    "\n",
    "# #筛选出2014年8月的用户记录\n",
    "# august_records = user_balance.filter(F.col(\"report_date\").between(\"20140801\", \"20140831\"))\n",
    "\n",
    "# #对每个用户进行分组，计算每个用户在2014年8月的记录天数\n",
    "# user_daily_counts = august_records.groupBy(\"user_id\").agg(\n",
    "#     F.countDistinct(\"report_date\").alias(\"days_active\")\n",
    "# )\n",
    "\n",
    "# #计算活跃用户数量\n",
    "# active_users = user_daily_counts.filter(F.col(\"days_active\") >= 5)\n",
    "# active_users_count = active_users.count()\n",
    "\n",
    "# print(\"The total number of active users in August 2014 is:\", active_users_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "fc88608b-e7d3-4040-bf22-a130415f547b",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2014年8月活跃用户总数： 12767\n"
     ]
    }
   ],
   "source": [
    "#任务1-2：统计活跃用户数量(基于spark RDD实现)\n",
    "\n",
    "#筛选2014年8月记录并简化\n",
    "august_record=user_balance_rdd_no_header.map(lambda x:x.split(\",\")).map(lambda x:(x[0],x[1])).filter(lambda x:x[1].startswith('201408'))\n",
    "\n",
    "# i=0\n",
    "# for date,user_id in august_record.collect():\n",
    "#     print(f\"{date}:{user_id}\")\n",
    "#     i+=1\n",
    "#     if i>100:\n",
    "#         break\n",
    "\n",
    "#映射为<id,1>，整合计数\n",
    "user_counts_rdd = august_record.map(lambda x: (x[0], 1)).reduceByKey(lambda a, b: a + b)\n",
    "active_users_rdd = user_counts_rdd.filter(lambda x: x[1] >= 5)\n",
    "active_users_count = active_users_rdd.count()\n",
    "print(\"2014年8月活跃用户总数：\",active_users_count)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "b073d6b0-e623-44fc-a4ed-9bbb9c9aae53",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+---+-------+-------------+\n|    _c0|_c1|    _c2|          _c3|\n+-------+---+-------+-------------+\n|user_id|sex|   city|constellation|\n|      2|  1|6411949|       狮子座|\n|     12|  1|6412149|       摩羯座|\n|     22|  1|6411949|       双子座|\n|     23|  1|6411949|       双鱼座|\n|     25|  1|6481949|       双鱼座|\n|     26|  1|6281949|       双子座|\n|     34|  0|6481949|       摩羯座|\n|     36|  1|6281949|       天秤座|\n|     42|  0|6281949|       水瓶座|\n|     47|  1|6412149|       天蝎座|\n|     49|  1|6081949|       摩羯座|\n|     53|  0|6411949|       天秤座|\n|     54|  0|6281949|       射手座|\n|     55|  0|6481949|       金牛座|\n|     58|  0|6412149|       天秤座|\n|     59|  0|6481949|       天秤座|\n|     62|  1|6412149|       天蝎座|\n|     65|  1|6412149|       白羊座|\n|     68|  0|6411949|       水瓶座|\n+-------+---+-------+-------------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "# File location and type\n",
    "file_location = \"/FileStore/tables/user_profile_table.csv\"\n",
    "file_type = \"csv\"\n",
    "\n",
    "# CSV options\n",
    "infer_schema = \"false\"\n",
    "first_row_is_header = \"false\"\n",
    "delimiter = \",\"\n",
    "\n",
    "# The applied options are for CSV files. For other file types, these will be ignored.\n",
    "user_profile = spark.read.format(file_type) \\\n",
    "  .option(\"inferSchema\", infer_schema) \\\n",
    "  .option(\"header\", first_row_is_header) \\\n",
    "  .option(\"sep\", delimiter) \\\n",
    "  .load(file_location)\n",
    "\n",
    "\n",
    "temp_table_name = \"user_profile_table_csv\"\n",
    "\n",
    "user_profile.createOrReplaceTempView(temp_table_name)\n",
    "\n",
    "\n",
    "user_profile.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "24bb0558-b245-4208-98bd-94818db9f085",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>_c2</th><th>avg_amt</th></tr></thead><tbody><tr><td>6281949</td><td>2795923.837298216</td></tr><tr><td>6301949</td><td>2650775.0664451825</td></tr><tr><td>6081949</td><td>2643912.7566638007</td></tr><tr><td>6481949</td><td>2087617.2136986302</td></tr><tr><td>6411949</td><td>1929838.5617977527</td></tr><tr><td>6412149</td><td>1896363.471625767</td></tr><tr><td>6581949</td><td>1526555.5551020408</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         "6281949",
         2795923.837298216
        ],
        [
         "6301949",
         2650775.0664451825
        ],
        [
         "6081949",
         2643912.7566638007
        ],
        [
         "6481949",
         2087617.2136986302
        ],
        [
         "6411949",
         1929838.5617977527
        ],
        [
         "6412149",
         1896363.471625767
        ],
        [
         "6581949",
         1526555.5551020408
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "_c2",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "avg_amt",
         "type": "\"double\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "--任务2-1统计城市的平均余额\n",
    "select up._c2,avg(ub.tbalance) as avg_amt from `user_balance_table_csv` as ub join `user_profile_table_csv` as up on ub.user_id=up._c0\n",
    "where ub.report_date=\"20140301\" group by up._c2 order by avg_amt desc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "952f4ac6-f1fa-4195-9e84-d0be0d2671a2",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>num_affected_rows</th><th>num_inserted_rows</th></tr></thead><tbody></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "num_affected_rows",
         "type": "\"long\""
        },
        {
         "metadata": "{}",
         "name": "num_inserted_rows",
         "type": "\"long\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "--任务2-2分步查询实现步1:生成中间表user_flow\n",
    "create table user_flow as\n",
    "select up._c2,ub.user_id,ub.total_purchase_amt,ub.total_redeem_amt,ub.report_date\n",
    "from `user_balance_table_csv` as ub join `user_profile_table_csv` as up on ub.user_id=up._c0\n",
    "where ub.report_date like \"201408%\" ;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "1716a9b3-98a2-4af6-8400-9cc89cacd4a4",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>_c2</th><th>user_id</th><th>total_flow</th></tr></thead><tbody><tr><td>6081949</td><td>27235</td><td>1.0847568E8</td></tr><tr><td>6081949</td><td>27746</td><td>7.6065458E7</td></tr><tr><td>6081949</td><td>18945</td><td>5.5304049E7</td></tr><tr><td>6281949</td><td>15118</td><td>1.49311909E8</td></tr><tr><td>6281949</td><td>11397</td><td>1.24293438E8</td></tr><tr><td>6281949</td><td>25814</td><td>1.04428054E8</td></tr><tr><td>6301949</td><td>2429</td><td>1.09171121E8</td></tr><tr><td>6301949</td><td>26825</td><td>9.537403E7</td></tr><tr><td>6301949</td><td>10932</td><td>7.4016744E7</td></tr><tr><td>6411949</td><td>662</td><td>7.5162566E7</td></tr><tr><td>6411949</td><td>21030</td><td>4.9933641E7</td></tr><tr><td>6411949</td><td>16769</td><td>4.9383506E7</td></tr><tr><td>6412149</td><td>22585</td><td>2.00516731E8</td></tr><tr><td>6412149</td><td>14472</td><td>1.3826279E8</td></tr><tr><td>6412149</td><td>25147</td><td>7.0594902E7</td></tr><tr><td>6481949</td><td>12026</td><td>5.1161825E7</td></tr><tr><td>6481949</td><td>670</td><td>4.9626204E7</td></tr><tr><td>6481949</td><td>14877</td><td>3.4488733E7</td></tr><tr><td>6581949</td><td>9494</td><td>3.8854436E7</td></tr><tr><td>6581949</td><td>26876</td><td>2.3449539E7</td></tr><tr><td>6581949</td><td>21761</td><td>2.113644E7</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         "6081949",
         "27235",
         1.0847568E8
        ],
        [
         "6081949",
         "27746",
         7.6065458E7
        ],
        [
         "6081949",
         "18945",
         5.5304049E7
        ],
        [
         "6281949",
         "15118",
         1.49311909E8
        ],
        [
         "6281949",
         "11397",
         1.24293438E8
        ],
        [
         "6281949",
         "25814",
         1.04428054E8
        ],
        [
         "6301949",
         "2429",
         1.09171121E8
        ],
        [
         "6301949",
         "26825",
         9.537403E7
        ],
        [
         "6301949",
         "10932",
         7.4016744E7
        ],
        [
         "6411949",
         "662",
         7.5162566E7
        ],
        [
         "6411949",
         "21030",
         4.9933641E7
        ],
        [
         "6411949",
         "16769",
         4.9383506E7
        ],
        [
         "6412149",
         "22585",
         2.00516731E8
        ],
        [
         "6412149",
         "14472",
         1.3826279E8
        ],
        [
         "6412149",
         "25147",
         7.0594902E7
        ],
        [
         "6481949",
         "12026",
         5.1161825E7
        ],
        [
         "6481949",
         "670",
         4.9626204E7
        ],
        [
         "6481949",
         "14877",
         3.4488733E7
        ],
        [
         "6581949",
         "9494",
         3.8854436E7
        ],
        [
         "6581949",
         "26876",
         2.3449539E7
        ],
        [
         "6581949",
         "21761",
         2.113644E7
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "_c2",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "user_id",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "total_flow",
         "type": "\"double\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql \n",
    "--任务2-2分步查询实现步2:分组排名并取前三\n",
    "SELECT _c2,user_id,t.total_flow\n",
    "FROM (\n",
    "    SELECT \n",
    "        _c2,\n",
    "        user_id,\n",
    "        SUM(total_purchase_amt + total_redeem_amt) AS total_flow,\n",
    "        ROW_NUMBER() OVER (PARTITION BY _c2 ORDER BY SUM(total_purchase_amt + total_redeem_amt) DESC) AS rank\n",
    "    FROM \n",
    "        `user_flow`\n",
    "    GROUP BY \n",
    "        user_id, _c2\n",
    ") t\n",
    "WHERE \n",
    "    t.rank <= 3\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "b810555c-ac60-41b4-b191-5dd1b3e597cd",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>_c2</th><th>user_id</th><th>month_total_flow</th></tr></thead><tbody><tr><td>6081949</td><td>27235</td><td>1.0847568E8</td></tr><tr><td>6081949</td><td>27746</td><td>7.6065458E7</td></tr><tr><td>6081949</td><td>18945</td><td>5.5304049E7</td></tr><tr><td>6281949</td><td>15118</td><td>1.49311909E8</td></tr><tr><td>6281949</td><td>11397</td><td>1.24293438E8</td></tr><tr><td>6281949</td><td>25814</td><td>1.04428054E8</td></tr><tr><td>6301949</td><td>2429</td><td>1.09171121E8</td></tr><tr><td>6301949</td><td>26825</td><td>9.537403E7</td></tr><tr><td>6301949</td><td>10932</td><td>7.4016744E7</td></tr><tr><td>6411949</td><td>662</td><td>7.5162566E7</td></tr><tr><td>6411949</td><td>21030</td><td>4.9933641E7</td></tr><tr><td>6411949</td><td>16769</td><td>4.9383506E7</td></tr><tr><td>6412149</td><td>22585</td><td>2.00516731E8</td></tr><tr><td>6412149</td><td>14472</td><td>1.3826279E8</td></tr><tr><td>6412149</td><td>25147</td><td>7.0594902E7</td></tr><tr><td>6481949</td><td>12026</td><td>5.1161825E7</td></tr><tr><td>6481949</td><td>670</td><td>4.9626204E7</td></tr><tr><td>6481949</td><td>14877</td><td>3.4488733E7</td></tr><tr><td>6581949</td><td>9494</td><td>3.8854436E7</td></tr><tr><td>6581949</td><td>26876</td><td>2.3449539E7</td></tr><tr><td>6581949</td><td>21761</td><td>2.113644E7</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         "6081949",
         "27235",
         1.0847568E8
        ],
        [
         "6081949",
         "27746",
         7.6065458E7
        ],
        [
         "6081949",
         "18945",
         5.5304049E7
        ],
        [
         "6281949",
         "15118",
         1.49311909E8
        ],
        [
         "6281949",
         "11397",
         1.24293438E8
        ],
        [
         "6281949",
         "25814",
         1.04428054E8
        ],
        [
         "6301949",
         "2429",
         1.09171121E8
        ],
        [
         "6301949",
         "26825",
         9.537403E7
        ],
        [
         "6301949",
         "10932",
         7.4016744E7
        ],
        [
         "6411949",
         "662",
         7.5162566E7
        ],
        [
         "6411949",
         "21030",
         4.9933641E7
        ],
        [
         "6411949",
         "16769",
         4.9383506E7
        ],
        [
         "6412149",
         "22585",
         2.00516731E8
        ],
        [
         "6412149",
         "14472",
         1.3826279E8
        ],
        [
         "6412149",
         "25147",
         7.0594902E7
        ],
        [
         "6481949",
         "12026",
         5.1161825E7
        ],
        [
         "6481949",
         "670",
         4.9626204E7
        ],
        [
         "6481949",
         "14877",
         3.4488733E7
        ],
        [
         "6581949",
         "9494",
         3.8854436E7
        ],
        [
         "6581949",
         "26876",
         2.3449539E7
        ],
        [
         "6581949",
         "21761",
         2.113644E7
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "_c2",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "user_id",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "month_total_flow",
         "type": "\"double\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "\n",
    "--任务2-2一步查询实现：\n",
    "select _c2,user_id,month_total_flow from (\n",
    "    select _c2,user_id,month_total_flow,row_number() over (partition by _c2 order by month_total_flow desc)as row_num\n",
    "  from(\n",
    "    select _c2,user_id,sum(total_flow) as month_total_flow\n",
    "    from (\n",
    "      select _c2,user_id,(b.total_purchase_amt+b.total_redeem_amt) as total_flow\n",
    "      from `user_profile_table_csv` as p\n",
    "        join `user_balance_table_csv` as b\n",
    "        on p._c0=b.user_id\n",
    "        where substr(b.report_date,1,6)='201408'\n",
    "        group by p._c2,b.user_id,b.report_date,b.total_purchase_amt,b.total_redeem_amt \n",
    "    )subquery1\n",
    "    group by _c2,user_id\n",
    "  )subquery2\n",
    ")subquery3\n",
    "where row_num <=3"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "environmentMetadata": null,
   "language": "python",
   "notebookMetadata": {
    "mostRecentlyExecutedCommandWithImplicitDF": {
     "commandId": 1186446197582003,
     "dataframes": [
      "_sqldf"
     ]
    },
    "pythonIndentUnit": 4
   },
   "notebookName": "lab4",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
